package com.dhgate.spu.analysis.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.dhgate.spu.analysis.bean.Attr;
import com.dhgate.spu.analysis.bean.Product;
import com.dhgate.spu.analysis.consts.SysConsts;
import com.dhgate.spu.analysis.util.StringUtil;

/**
 * @author: wangguodong
 * @create: 2014-2-13 上午11:44:26
 * @description:
 */
public class JdbcDAO implements IDAO {

	protected static final Logger logger = LoggerFactory.getLogger(JdbcDAO.class);

	public  List<Attr> getAttrList(String prod_line_id,String attrIds) {
		List<Attr> attrList = new ArrayList<Attr>();
		StringBuffer sql = new StringBuffer();

		sql.append("select attr_id, line_attr_name, line_attr_name_cn from td_prodline_attr where prod_line_id= '"+prod_line_id+"' and valid = 1  and attr_id in(" +attrIds+")");
		JDBCUtil jdbcutil = null;
		ResultSet rs = null;
		try {
			jdbcutil = getOracleJDBCUtil();
			rs = jdbcutil.executeQuery(sql.toString());
			while (rs.next()) {
				Attr attr = new Attr();
				attr.setAttrId(rs.getLong("attr_id"));
				attr.setAttrName(rs.getString("line_attr_name"));
				attr.setAttrCNName(rs.getString("line_attr_name_cn"));
				attr.setAttrValIds(getAttrValIds(attr.getAttrId()));
				attrList.add(attr);
			}
			
			if(attrList.size()==0){
				return attrList;
			}
			
			
		} catch (Exception e) {
			logger.error("JDBCProductDAO:getAttrList e : " + e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
			}
			if (jdbcutil != null) {
				jdbcutil.closePrepStmt();
			}
		}
		return attrList;
	}
	
	
	
	
	
	private static List<Long> getAttrValIds(long attrId){
		List<Long> attrValIdList = new ArrayList<Long>();
		StringBuffer sql = new StringBuffer();

		sql.append("select attr_val_id,line_attrval_name, line_attrval_name_cn from td_prodline_attrval where attr_id ='"+attrId+"' and valid = 1");
		JDBCUtil jdbcutil = null;
		ResultSet rs = null;
		try {
			jdbcutil = getOracleJDBCUtil();
			rs = jdbcutil.executeQuery(sql.toString());
			while (rs.next()) {
				attrValIdList.add(rs.getLong("attr_val_id"));			
			}	
		} catch (Exception e) {
			logger.error("getAttrValIds() e : " + e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
			}
			if (jdbcutil != null) {
				jdbcutil.closePrepStmt();
			}
		}
		
		return attrValIdList;
	}
	
	/**
	 * select attr_val_id,line_attrval_name from hggjv2.td_cate_pub_attrval where cate_pub_attr_id = (
		select cate_pub_attr_id from hggjv2.td_cate_pub_attr where attr_id = '986601'
		)

	 */
	
	public Map<String, String>  getAttrValMap(String attrIds){
		return null;
	}
	
	public Map<String, String>  getAttrValMap(String cateId,String attrIds){
		Map<String,String> map = new HashMap<String,String>();
		StringBuffer sql = new StringBuffer();

		sql.append("select attr_val_id,line_attrval_name, line_attrval_name_cn from hggjv2.td_cate_pub_attrval where cate_pub_attr_id in (select cate_pub_attr_id from hggjv2.td_cate_pub_attr where attr_id = "+attrIds+" and cate_pub_id="+cateId+")");
		JDBCUtil jdbcutil = null;
		ResultSet rs = null;
		try {
			jdbcutil = getOracleJDBCUtil();
			rs = jdbcutil.executeQuery(sql.toString());
			while (rs.next()) {
				map.put(rs.getString("attr_val_id"),rs.getString("line_attrval_name").toLowerCase());			
			}	
		} catch (Exception e) {
			logger.error("getAttrValIds() e : " + e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
			}
			if (jdbcutil != null) {
				jdbcutil.closePrepStmt();
			}
		}
		
		return map;
	}

	/**
	 * 判断是否子属性
	 */
	@Override
	public boolean isChildAttr(String attrId) {
		int count = 0;
		StringBuffer sql = new StringBuffer();

		sql.append("select count(1) from hggjv2.TD_ATTR_CROSSLINK where child_attr_id="+attrId);
		JDBCUtil jdbcutil = null;
		ResultSet rs = null;
		try {
			jdbcutil = getOracleJDBCUtil();
			rs = jdbcutil.executeQuery(sql.toString());
			while (rs.next()) {
				count = rs.getInt(1);
			}	
		} catch (Exception e) {
			logger.error("isChildAttr() e : " + e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
			}
			if (jdbcutil != null) {
				jdbcutil.closePrepStmt();
			}
		}
		
		return count > 0;
	}
	
	/**
	 * 获取是否多选属性、单选属性
	 */
	@Override
	public String getAttrType(String attrId) {
		String type = "";
		StringBuffer sql = new StringBuffer();

		sql.append("select type from hggjv2.td_cate_pub_attr t where t.attr_id="+attrId);
		JDBCUtil jdbcutil = null;
		ResultSet rs = null;
		try {
			jdbcutil = getOracleJDBCUtil();
			rs = jdbcutil.executeQuery(sql.toString());
			while (rs.next()) {
				type = rs.getString("type");
			}	
		} catch (Exception e) {
			logger.error("getAttrType() e : " + e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
			}
			if (jdbcutil != null) {
				jdbcutil.closePrepStmt();
			}
		}
		
		return type;
	}
	
	/**
	 * 获取属性值组
	 */
	@Override
	public Map<String, String> getAttrvalGroupMap(String childAttrId, String parentAttrValId) {
		Map<String, String> map = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer();

		sql.append("select attr_val_id,line_attrval_name, line_attrval_name_cn from hggjv2.td_prodline_attrval where attr_val_id in (select attrval_id from hggjv2.td_attrval_group_attrval where attrval_group_id="+
				 "(select attrval_group_id from hggjv2.td_attrval_group where child_attr_id="+childAttrId+" and parent_attrval_id="+parentAttrValId+") )");

		JDBCUtil jdbcutil = null;
		ResultSet rs = null;
		try {
			jdbcutil = getOracleJDBCUtil();
			rs = jdbcutil.executeQuery(sql.toString());
			while (rs.next()) {
				map.put(rs.getString("attr_val_id"),rs.getString("line_attrval_name").toLowerCase());			
			}
			
		} catch (Exception e) {
			logger.error("getAttrvalGroup() e : " + e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
			}
			if (jdbcutil != null) {
				jdbcutil.closePrepStmt();
			}
		}
		
		return map;
	}
	
	/**
	 * 获取父属性Id   
	 */
	@Override
	public String getParentAttr(String attrId) {
		String parentAttr = "";
		StringBuffer sql = new StringBuffer();

		sql.append("select parent_attr_id from hggjv2.TD_ATTR_CROSSLINK where child_attr_id="+attrId);

		JDBCUtil jdbcutil = null;
		ResultSet rs = null;
		try {
			jdbcutil = getOracleJDBCUtil();
			rs = jdbcutil.executeQuery(sql.toString());
			while (rs.next()) {
				parentAttr = rs.getString("parent_attr_id");
			}	
		} catch (Exception e) {
			logger.error("getAttrvalGroup() e : " + e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
			}
			if (jdbcutil != null) {
				jdbcutil.closePrepStmt();
			}
		}
		
		return parentAttr;
	}
	
	
	public List<Product> getProducts(String l2sid, String prodLineId, String attrValIds) {
		List<Product> pList = new ArrayList<Product>();
		StringBuffer itemcodeSB = new StringBuffer();
		StringBuffer sql = new StringBuffer();
		sql.append("select p.itemcode, pb.productname,pb.shortdescription,pb.keywords, p.prod_line_id,p.supplierid ");
		sql.append("from product_"+l2sid+" p, product_base_"+l2sid+" pb ");
		sql.append("where p.prod_line_id = "+prodLineId+" and p.istate = 1  and pb.itemcode=p.itemcode");

//		logger.debug("getProducts sql:"+sql.toString());
		JDBCUtil jdbcutil = null;
		ResultSet rs = null;
		try {
			jdbcutil = getMySqlJDBCUtil(l2sid);
			rs = jdbcutil.executeQuery(sql.toString());
			while (rs.next()) {
				Product p = new Product();
				long itemcode = rs.getLong("itemcode");
				p.setItemCode(itemcode);
				p.setProductName(rs.getString("productname"));
				p.setProdLineId(rs.getString("prod_line_id"));
				p.setSupplierid(rs.getString("supplierid"));
				p.setShortDescription(rs.getString("shortdescription"));
				p.setKeywords(rs.getString("keywords"));
				pList.add(p);	
				itemcodeSB.append(itemcode+",");
			}	
		} catch (Exception e) {
			logger.error("JdbcDAO.getPruducts e : " + e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
			}
			if (jdbcutil != null) {
				jdbcutil.closePrepStmt();
			}
		}
		
		if(itemcodeSB.length()==0){
			return pList;
		}
		
		
		StringBuffer inSql = new StringBuffer();
		inSql.append("select p.itemcode ");
		inSql.append("from product_"+l2sid+" p ");
		inSql.append("where p.prod_line_id = "+prodLineId+" and p.istate = 1");
		
		sql = new StringBuffer();
//		select itemcode ,attr_id, attr_val_id from product_attrval_40 where itemcode in (select itemcode from product_40 where prod_line_id = 19960)
		sql.append("select itemcode ,attr_id, attr_val_id ");
		sql.append("from product_attrval_"+l2sid+" ");
//		sql.append("where itemcode in ("+itemcodeSB.substring(0,itemcodeSB.length()-1)+") and attr_id in("+attrIds+")");
		sql.append("where exists ("+inSql.toString()+") and attr_val_id in("+attrValIds+")");
        Map<Long,List<Attr>> attrsMap = new HashMap<Long,List<Attr>>();
        Map<String,Attr> icAttrIdMap = new HashMap<String,Attr>();
		try {
			jdbcutil = getMySqlJDBCUtil(l2sid);
			rs = jdbcutil.executeQuery(sql.toString());
			while (rs.next()) {
				Long itemcode = rs.getLong("itemcode");
				Long attrId = rs.getLong("attr_id");
				String key = ""+itemcode+"-"+attrId;
				Attr attr = icAttrIdMap.get(key);
				if(attr == null){
					attr = new Attr();
					attr.setAttrId(rs.getLong("attr_id"));	
					attr.setAttrValIds(new ArrayList<Long>());
					icAttrIdMap.put(key, attr);
					List<Attr> attrList = attrsMap.get(itemcode);
					if(attrList == null){
						attrList = new ArrayList<Attr>();
						attrsMap.put(itemcode, attrList);
					}
					attrList.add(attr);
				}
				attr.getAttrValIds().add(rs.getLong("attr_val_id"));
			}	
		} catch (Exception e) {
			logger.error("JdbcDAO.getPruducts e : " + e);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
			}
			if (jdbcutil != null) {
				jdbcutil.closePrepStmt();
			}
		}
		
		for(Product p : pList){
			Set<Long> attrValIdSet = new HashSet<Long>();
			List<Attr> attrList = attrsMap.get(p.getItemCode());
			if(attrList == null || attrList.size() == 0){
				continue;
			}
			Map<Long,List<Long>> attrMap = new HashMap<Long,List<Long>>();
			for(Attr attr: attrList){
				Collections.sort(attr.getAttrValIds());
				attrMap.put(attr.getAttrId(), attr.getAttrValIds());
				//把所有已经查到的属性放入solr中方便查询
			    p.setField(attr.getAttrId()+"id",attr.getAttrValIds().toString()+"-");
				for(Long attrValId : attr.getAttrValIds()){
					attrValIdSet.add(attrValId);
			}
			}
			p.setAttrMap(attrMap);
			p.setAttrValIdSet(attrValIdSet);
		}
		return pList;
	}

	private static JDBCUtil getMySqlJDBCUtil(String l2sid) {
		JDBCUtil jdbcUtil = null;
		String url = null;
		url = SysConsts.DBURL_MAP.get(l2sid);

		if (StringUtil.isNotEmpty(url)) {
			ConnectionPool pool = null;
			try {
				if (SysConsts.CONNPOOLMAP.containsKey(url)) {
					pool = SysConsts.CONNPOOLMAP.get(url);
				} else {
					ConnectionPoolAbs poolabs = SysConsts.CONNPOOLABSMAP.get(url);
					pool = new ConnectionPool(poolabs);

					pool.createPool();
					SysConsts.CONNPOOLMAP.put(url, pool);
				}

				jdbcUtil = new JDBCUtil(pool);
			} catch (Exception e) {
				logger.error("", e);
			}
		}
		return jdbcUtil;
	}

	private static JDBCUtil getOracleJDBCUtil() {
		JDBCUtil jdbcUtil = null;
		ConnectionPoolAbs poolabs = null;
		String url = null;

		url = SysConsts.DBURL_MAP.get("oracle");

		if (StringUtil.isNotEmpty(url)) {
			try {
				ConnectionPool pool = null;
				if (SysConsts.CONNPOOLMAP.containsKey(url)) {
					pool = SysConsts.CONNPOOLMAP.get(url);
				} else {
					poolabs = SysConsts.CONNPOOLABSMAP.get(url);
					pool = new ConnectionPool(poolabs);

					pool.createPool();
					SysConsts.CONNPOOLMAP.put(url, pool);
				}
			
				jdbcUtil = new JDBCUtil(pool);

			} catch (Exception e) {
				logger.error("", e);
			}
		}
		return jdbcUtil;
	}

}
